/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.absisgroup.nt;

import com.absisgroup.nsc.utilities.GenericServlet;
import com.absisgroup.nsc.utilities.Util;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.json.JSONArray;
import org.json.JSONObject;

/**
 *
 * @author Hue
 */
public class LuuPhieuNhapKho extends GenericServlet {

    @Override
    public int process(final Connection con, final HttpServletRequest request, final HttpServletResponse response) throws Exception {
        //debug [
        BigDecimal brPK = new BigDecimal(1);
        //]
        JSONArray records = new JSONArray(request.getParameter("records"));
        
        save(con, brPK, request, records);
        con.commit();
        
        final JSONObject jsObj = new JSONObject();
        jsObj.put("success", true);
        
        writeToResponse(response, jsObj.toString());

        return DONT_RUN_JSP;
    }

    private void save(Connection con, BigDecimal brPK, HttpServletRequest request, JSONArray records) throws Exception {
        Timestamp date = Util.newTimestamp();
        String whName = request.getParameter("whName");
        String whAddress = request.getParameter("whAddress");
        String whPhone = request.getParameter("whPhone");
        String toWhType = request.getParameter("toWhType");
        String comment = request.getParameter("comment");
        int impProdNum = getImpProdNum(con);
        String impProdNo = lpad(impProdNum);
        BigDecimal whPK = getWhPK(con, toWhType, brPK);

        String strQuery = "insert into IMPORT_PRODUCTS(IMP_PROD_DATE, IMP_PROD_TO_WH_FK, IMP_PROD_FROM_WH_NAME, IMP_PROD_FROM_WH_ADDRESS, IMP_PROD_FROM_WH_PHONE, IMP_PROD_COMMENT, IMP_PROD_NUMBER, IMP_PROD_NO) " + 
                          "values(?, ?, ?, ?, ?, ?, ?, ?)";
        PreparedStatement pStmt = con.prepareStatement(strQuery);
        int iiIndex = 1;
        pStmt.setTimestamp(iiIndex++, date);
        pStmt.setBigDecimal(iiIndex++, whPK);
        pStmt.setString(iiIndex++, whName);
        pStmt.setString(iiIndex++, whAddress);
        pStmt.setString(iiIndex++, whPhone);
        pStmt.setString(iiIndex++, comment);
        pStmt.setInt(iiIndex++, impProdNum);
        pStmt.setString(iiIndex++, impProdNo);
        pStmt.executeUpdate();
        pStmt.close();
        
        pStmt = con.prepareStatement("SELECT LAST_INSERT_ID()");
        ResultSet rs = pStmt.executeQuery();
        BigDecimal impProdPK = null;
        if(rs.next()) {
            impProdPK = rs.getBigDecimal(1);
        }
        pStmt.close();

        strQuery = "insert into IMP_PROD_DETAILS(IMP_PROD_DET_STT, IMP_PROD_DET_IMP_PROD_FK, IMP_PROD_DET_PROD_FK, IMP_PROD_DET_SO_KG, IMP_PROD_DET_SO_MET, IMP_PROD_DET_DON_GIA, IMP_PROD_DET_SO_KG_REMAINING, IMP_PROD_DET_SO_MET_REMAINING) " + 
                   "values(?, ?, ?, ?, ?, ?, ?, ?)";
        pStmt = con.prepareStatement(strQuery);
        int iLength = records.length();
        for(int i=0; i<iLength; i++) {
            JSONObject jsObj = records.getJSONObject(i);
            int iIndex = 1;
            pStmt.setInt(iIndex++, jsObj.getInt("stt"));
            pStmt.setBigDecimal(iIndex++, impProdPK);
            pStmt.setString(iIndex++, jsObj.getString("prodPK"));
            final String soKg = jsObj.getString("soKg");
            final String soMet = jsObj.getString("soMet");
            pStmt.setString(iIndex++, soKg);
            pStmt.setString(iIndex++, soMet);
            pStmt.setString(iIndex++, jsObj.getString("donGiaNhap"));
            pStmt.setString(iIndex++, soKg);
            pStmt.setString(iIndex++, soMet);
            pStmt.addBatch();
        }
        pStmt.executeBatch();
        pStmt.close();
    }
    
    private BigDecimal getWhPK(Connection con, String toWhType, BigDecimal brPK) throws SQLException {
        String strQuery = "select WH_PK from WAREHOUSE where WH_TYPE = ? and WH_BR_FK = ?";
        PreparedStatement pStmt = con.prepareStatement(strQuery);
        pStmt.setString(1, toWhType);
        pStmt.setBigDecimal(2, brPK);
        ResultSet rs = pStmt.executeQuery();
        BigDecimal whPK = null;
        if(rs.next()) {
            whPK = rs.getBigDecimal(1);
        }
        pStmt.close();
        
        return whPK;
    }
    
    private int getImpProdNum(Connection con) throws SQLException {
        String strQuery = "select max(IMP_PROD_NUMBER) + 1 from IMPORT_PRODUCTS";
        PreparedStatement pStmt = con.prepareStatement(strQuery);
        ResultSet rs = pStmt.executeQuery();
        int num = 1;
        if(rs.next()) {
            num = rs.getInt(1);
        }
        pStmt.close();
        
        return (num <= 0 ? 1 : num);
    }

    private String lpad(int value) {
        int maxLength = 9;
        String str = String.valueOf(value);
        while(str.length() < maxLength) {
            str = "0" + str;
        }
        return 'A' + str;
    }
    
    @Override
    protected boolean useConnection() {
        return true;
    }
}
